
package com.compuvest.admin.chart;

import com.compuvest.admin.chart.xml.ChartRecord;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class ChartMySQL implements Serializable {

    private final static String VERSION = "ChartMySQL v2.0 2008-13-02";

    private Hashtable<String,String> users;

    private DataSource ds;

    public ChartMySQL() {
        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception e) { System.out.println("Error ChartMySQL::init\n" + e); }

        users = getUsers();

        System.out.println("Init: " + VERSION);
    }

    public Hashtable<String,String> getUsers() {

        Hashtable<String,String> usersList = new Hashtable<String,String>(11);
        PreparedStatement us = null;
        ResultSet rs = null;
        try(Connection conn = ds.getConnection()) {

            us = conn.prepareStatement("SELECT alias,users_id FROM chart.users");
            rs = us.executeQuery();
            while(rs.next()) {
                usersList.put(rs.getString("alias"), rs.getString("users_id"));
            }
        }
        catch(SQLException e) { System.out.println("\nError ChartMySQL::getUsers\n" + e); }
        finally {
            if(rs != null) try { rs.close(); rs = null; } catch(SQLException ignore) { }
            if(us != null) try { us.close(); us = null; } catch(SQLException ignore) { }
        }
        return usersList;
    }


    public String updateStock(ArrayList<ChartRecord> records) {

	PreparedStatement us = null;
	ResultSet rs = null;
        try(Connection conn = ds.getConnection()) {

	    us = conn.prepareStatement(
	        "REPLACE LOW_PRIORITY chart.days SET date=?,users_id=?,days_30=?,days_60=?,days_90=?,days_180=?,days_240=?,days_more=?");
	    for(ChartRecord chart : records) {
		us.setString(1, chart.getDate());
		us.setString(2, users.get(chart.getUserName()));
		us.setDouble(3, chart.getDays30());
		us.setDouble(4, chart.getDays60());
		us.setDouble(5, chart.getDays90());
		us.setDouble(6, chart.getDays180());
		us.setDouble(7, chart.getDays240());
		us.setDouble(8, chart.getDaysMore());
		us.addBatch();
	    }
    	    us.executeBatch();
        }
	catch(SQLException e) { System.out.println("\nError ChartMySQL::updateStock\n" + e); }
	finally {
	    if(rs != null) try { rs.close(); rs = null; } catch(SQLException ignore) { }
	    if(us != null) try { us.close(); us = null; } catch(SQLException ignore) { }
	}

	return "OK";
    }

    public String getStock(String date) {

	StringBuilder xml = new StringBuilder(5000);
        xml.append("<?xml version='1.0' encoding='UTF-8' ?>");
        xml.append("<Chart>");

	PreparedStatement us = null;
	ResultSet rs = null;
        try(Connection conn = ds.getConnection()) {

	    us = conn.prepareStatement(
		"SELECT date,days_30,days_60,days_90,days_180,days_240,days_more,name " +
                "FROM chart.days LEFT JOIN chart.users USING(users_id) WHERE date=?");
            us.setString(1, date);
	    rs = us.executeQuery();
	    while(rs.next()) {
                xml.append("<User name='").append(rs.getString("name")).append("' date='").append(rs.getString("date")).append("'>\n");
                xml.append("<Days_30>").append(rs.getString("days_30")).append("</Days_30>");
                xml.append("<Days_60>").append(rs.getString("days_60")).append("</Days_60>");
                xml.append("<Days_90>").append(rs.getString("days_90")).append("</Days_90>");
                xml.append("<Days_180>").append(rs.getString("days_180")).append("</Days_180>");
                xml.append("<Days_240>").append(rs.getString("days_240")).append("</Days_240>");
                xml.append("<Days_more>").append(rs.getString("days_more")).append("</Days_more>");
                xml.append("</User>");
	    }
    	    xml.append("</Chart>");
        }
	catch(SQLException e) { System.out.println("\nError ChartMySQL::getStock\n" + e); }
	finally {
	    if(rs != null) try { rs.close(); rs = null; } catch(SQLException ignore) { }
	    if(us != null) try { us.close(); us = null; } catch(SQLException ignore) { }
	}

        return xml.toString();
    }

//------------------------------------------------
//
    public String getUserStock(String userName) {

	StringBuilder xml = new StringBuilder(5000);
        xml.append("<?xml version='1.0' encoding='UTF-8' ?>");
        xml.append("<Chart>");

	PreparedStatement us = null;
	ResultSet rs = null;
        try(Connection conn = ds.getConnection()) {

	    us = conn.prepareStatement(
		"SELECT DAY(date) day,MONTH(date) month,YEAR(date) year,days_30,days_60,days_90,days_180,days_240,days_more,name " +
                "FROM chart.days LEFT JOIN chart.users USING(users_id) WHERE name=?");
            us.setString(1, userName);
	    rs = us.executeQuery();
	    while(rs.next()) {
                xml.append("<User name='").append(rs.getString("name")).append("' day='").append(rs.getString("day"));
                xml.append("' month='").append(rs.getString("month")).append("' year='").append(rs.getString("year")).append("'>\n");
                xml.append("<Days_30>").append(rs.getString("days_30")).append("</Days_30>");
                xml.append("<Days_60>").append(rs.getString("days_60")).append("</Days_60>");
                xml.append("<Days_90>").append(rs.getString("days_90")).append("</Days_90>");
                xml.append("<Days_180>").append(rs.getString("days_180")).append("</Days_180>");
                xml.append("<Days_240>").append(rs.getString("days_240")).append("</Days_240>");
                xml.append("<Days_more>").append(rs.getString("days_more")).append("</Days_more>");
                xml.append("</User>");
	    }
    	    xml.append("</Chart>");
        }
	catch(SQLException e) { System.out.println("\nError ChartMySQL::getUserStock\n" + e); }
	finally {
	    if(rs != null) try { rs.close(); rs = null; } catch(SQLException ignore) { }
	    if(us != null) try { us.close(); us = null; } catch(SQLException ignore) { }
	}
	return xml.toString();
    }

}
